﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Xml;
using Microsoft.Win32;

namespace WEBCienciaDeporte
{
    public class ClsConeccion
    {
           private  SqlConnection _cn;
        private readonly string _base;
        private readonly string _servidor;
        private readonly string _usuario;
        private readonly string _pwd;
        private readonly string _conecctionString;
        private   List<SqlParameter> _parametros;

        public SqlConnection Cn
        {
            get { return Cn = _cn; }
            set { _cn = value; }
        }
        public ClsConeccion ()
        {
            //_conecctionString = "data source=.;integrated security=SSPI;initial catalog=w1750828_Metro";
            _conecctionString = "data source=.;integrated security=SSPI;initial catalog=wi711088_web";
           // _conecctionString = "data source= " + @"NOTEBOOK-PC\SQLEXPRESS" + ";User ID=sa;Pwd = hunter;initial catalog=RosarioFinanzas";
            var sqlCn = new SqlConnection(_conecctionString);
            Cn = sqlCn;
 
        }
        public void Abrir()
        {
            Cn.Open();
            _parametros = new List<SqlParameter>();
        }
        public void Cerrar()
        {
            Cn.Close();
        }
        public int EjecutarSql (string strsql )
        {
            try
            {
                var cmd = new SqlCommand {Connection = Cn, CommandText = strsql};
                return cmd.ExecuteNonQuery();
             
            }
            catch(Exception)
            {
                return -1;
            }

        }
        public SqlDataReader EjecutarSqlDataReader (string strsql)
        {
            var cmd = new SqlCommand { Connection = Cn, CommandText = strsql };
            return cmd.ExecuteReader();
        }
        public XmlTextReader  EjecutarSqlXmlDataReader(string strsql)
        {
            var cmd = new SqlCommand { Connection = Cn, CommandText = strsql };
            return  (XmlTextReader) cmd.ExecuteXmlReader();
        }
        public Int16 EjecutarSp (string strStoreProcedure)
        {
            var cmd = new SqlCommand
                          {
                              Connection = Cn,
                              CommandText = strStoreProcedure,
                              CommandType = CommandType.StoredProcedure
                          };
            foreach (var obj in _parametros)
            {
                cmd.Parameters.Add(obj);
            }
            cmd.ExecuteNonQuery();
            return 0;
        }
        public SqlDataReader EjecutarSpDataReader(string strStoreProcedure)
        {
            var cmd = new SqlCommand
            {
                Connection = Cn,
                CommandText = strStoreProcedure,
                CommandType = CommandType.StoredProcedure
            };
            foreach (var obj in _parametros)
            {
                cmd.Parameters.Add(obj);
            }
            return cmd.ExecuteReader();
            
        }
        public void AgregarParametros(string strNombre, object objValue)
        {
            var obj = new SqlParameter(strNombre,objValue);
            _parametros.Add(obj);

        }

    }
}
